<?php
/**
 * 统计专用model
 * @author: monster
 * @day: 2022/5/20
 */

namespace app\admin\model\system;

use traits\ModelTrait;
use basic\ModelBasic;
use app\wap\model\wap\VisitLog;
use app\admin\model\user\User;
use app\admin\model\order\StoreOrder;
use app\admin\model\special\SpecialSubject;
use app\admin\model\special\Special;
use service\PHPExcelService;

/**
 * Class Express
 * @package app\admin\model\system
 */
class Statistics extends ModelBasic
{
    use ModelTrait;

    /**
     * [userVisitCount 用户访问量统计]
     * @Author   monster
     * @DateTime 2022-05-20T14:40:28+0800
     * @param    [type]                   $start_time [description]
     * @param    [type]                   $end_time   [description]
     * @return   [type]                               [description]
     */
    public static function userVisitCount($start_time = 0, $end_time = 0, $module = 'wap', $controller = '', $action = '', $is_group = 0, $id = 0)
    {
        $model = new VisitLog();
        if ($start_time && $end_time) {
            $model = $model->where('add_time', 'between time', [$start_time, $end_time]);
        }
        if ($controller && $action) {
            $model = $model->where(['controller' => $controller, 'action' => $action]);
        }
        if ($id) {
            $model = $model->where('param_id', $id);
        }

        if ($is_group) {
            $model = $model->group('ip');
        }
        return $model->count();
    }
    
    /**
     * [userActiveCount 活跃人数]
     * @Author   monster
     * @DateTime 2022-05-23T17:24:29+0800
     * @param    [type]                   $start_time [description]
     * @param    [type]                   $end_time   [description]
     * @return   [type]                               [description]
     */
    public static function userActiveCount($start_time, $end_time)
    {
        return User::where('last_time', 'between time', [$start_time, $end_time])->count();
        return VisitLog::where('add_time', 'between time', [$start_time, $end_time])->group('ip')->count();
    }

    /**
     * [newUserCount 新增用户数]
     * @Author   monster
     * @DateTime 2022-05-20T14:45:23+0800
     * @param    [type]                   $start_time [description]
     * @param    [type]                   $end_time   [description]
     * @return   [type]                               [description]
     */
    public static function newUserCount($start_time, $end_time, $reg_pro = '')
    {
        $model = new User();
        $model = $model->where('add_time', 'between time', [$start_time, $end_time])->where(['status'=>1, 'is_del'=>0]);
        if ($reg_pro) {
            $model = $model->where('reg_pro', $reg_pro);
        }
        return $model->count();
    }

    /**
     * [userOrderCount 用户订单成交数]
     * @Author   monster
     * @DateTime 2022-05-23T17:31:34+0800
     * @return   [type]                   [description]
     */
    public static function userOrderCount($start_time = 0, $end_time = 0)
    {
        $model = new StoreOrder();
        $model = $model->where(['is_del' => 0, 'refund_status' => 0, 'paid' => 1]);
        if ($start_time && $end_time) {
            $model = $model->where('add_time', 'between time', [$start_time, $end_time]);
        }
        return $model->group('uid')->count();
    }

    /**
     * [userBuyCount 用户购买次数]
     * @Author   monster
     * @DateTime 2022-05-23T18:02:13+0800
     * @return   [type]                   [description]
     */
    public static function userBuyCount()
    {
        return [
            'p0'  => User::field('uid')->where('pay_count',0)->count(),
            'p1'  => User::field('uid')->where('pay_count',1)->count(),
            'p2p' => User::field('uid')->where('pay_count > 1')->count(),
        ];
        return StoreOrder::where(['is_del' => 0, 'refund_status' => 0, 'total_num' => 1, 'paid' => 1])->field('count(*) as user_buy_count,uid')->group('uid')->select()->toArray();
    }

    /**
     * [userCount 用户数量统计]
     * @Author   monster
     * @DateTime 2022-05-26T10:00:01+0800
     * @param    string                   $pro [省]
     * @return   [type]                        [description]
     */
    public static function userCount($pro = '')
    {
        $where = ['status'=>1, 'is_del'=>0];
        if ($pro) {
            $where['reg_pro'] = $pro;
        }
        return User::where($where)->count();
    }
    
    /**
     * [userPro 用户省份排名]
     * @Author   monster
     * @DateTime 2022-05-25T17:33:36+0800
     * @return   [type]                   [description]
     */
    public static function userPro($limit = 10)
    {
        return User::where(['status'=>1, 'is_del'=>0])->where("reg_pro is not null and reg_pro != ''")->field('count(uid) as user_count,reg_pro')->group('reg_pro')->order('user_count desc')->limit($limit)->select()->toArray();
    }

    /**
     * [userVisitCountByPro 用户地区访客数]
     * @Author   monster
     * @DateTime 2022-05-25T17:50:34+0800
     * @return   [type]                   [description]
     */
    public static function userVisitCountByPro($start_time, $end_time, $pro = '')
    {
        return VisitLog::where('add_time', 'between time', [$start_time, $end_time])->where('pro', $pro)->count();
    }

    /**
     * [userPayByPro 用户地区支付金额]
     * @Author   monster
     * @DateTime 2022-05-25T17:53:37+0800
     * @param    [type]                   $start_time [description]
     * @param    [type]                   $end_time   [description]
     * @param    string                   $pro        [description]
     * @return   [type]                               [description]
     */
    public static function userPayByPro($start_time, $end_time, $pro = '')
    {
        return User::where('reg_pro' , $pro)->group('reg_pro')->sum('pay_amt');

        $model = StoreOrder::where(['o.is_del' => 0, 'o.refund_status' => 0, 'o.total_num' => 1, 'o.paid' => 1])->alias('o')->field('sum(o.pay_price) as sales');
        if ($pro) {
            $model = $model->where('u.reg_pro', $pro);
        }
        if ($start_time && $end_time) {
            $model = $model->where('o.add_time', 'between', [$start_time, $end_time]);
        }
        $model = $model->join('__USER__ u', 'u.uid=o.uid', 'left');

        $data = ($data = $model->select()) && count($data) ? $data->toArray()[0]['sales'] : 0;

        return $data ? $data : "0.00";
    }

    /**
     * [orderCount 订单数量]
     * @Author   monster
     * @DateTime 2022-05-20T15:10:16+0800
     * @param    [type]                   $start_time [description]
     * @param    [type]                   $end_time   [description]
     * @return   [type]                               [description]
     */
    public static function orderCount($start_time = 0, $end_time = 0, $type = 0, $cart_ids = array(), $refund_status = 0, $from_page = 0)
    {
        $model = new StoreOrder();
        $model = $model->where(['is_del' => 0, 'refund_status' => $refund_status, 'total_num' => 1, 'paid' => 1]);
        if ($start_time && $end_time) {
            $model = $model->where('add_time', 'between time', [$start_time, $end_time]);
        }
        if ($type) {
            switch ($type) {
                // 兑换订单
                case 1: 
                    $model = $model->where('mark', '兑换码订单');
                    break;
                // 拼团订单
                case 2: 
                    $model = $model->where('combination_id', '>', 0)->where('pink_id', '>', 0);
                    break;
                // 礼物订单
                case 3:
                    $model = $model->where('is_gift', 1);
                    break;
                // 秒杀订单
                case 4:
                    $model = $model->where('seckill_id', '>', 0);
                    break;
            }
        }
        if (!empty($cart_ids)) {
            if (is_array($cart_ids)) {
                $model = $model->where('cart_id', 'in', $cart_ids);
            } else {
                $model = $model->where('cart_id', $cart_ids);
            }
        }

        if ($from_page) {
            $model = $model->where('order_from', $from_page);
        }

        return $model->count();
    }

    /**
     * [orderAmount 订单金额]
     * @Author   monster
     * @DateTime 2022-05-20T15:10:06+0800
     * @param    [type]                   $start_time [description]
     * @param    [type]                   $end_time   [description]
     * @return   [type]                               [description]
     */
    public static function orderAmount($start_time = 0, $end_time = 0, $pay_type = '', $cart_ids = array(), $refund_status = 0)
    {
        $where = sprintf(' is_del = 0 AND refund_status = %d AND paid = 1' , $refund_status);
        $yueLeft = 0;

        if ($pay_type) {
            $where .= sprintf(" AND pay_type = '%s'" , $pay_type);
        }
        if ($start_time && $end_time) {
            $where .= sprintf(" AND add_time BETWEEN %d AND %d" , $start_time , $end_time);
        }
        if ($cart_ids) {
            $where .= sprintf(" AND cart_id IN (%s)" , join(',' , $cart_ids));
        }

        if ( $pay_type == 'yue' || !$pay_type ) {
            $where1 = str_replace("AND pay_type = 'yue'", '', $where);
            $yueLeftM = StoreOrder::field('sum(combine_pay_amt) as total_price')->where($where1)->where('pay_type' , 'IN' , ['zhifubao','weixin'])->where('is_combine = 1')->find();
            $yueLeft  = $yueLeftM->total_price + 0;
        }

        $res = StoreOrder::field('sum(pay_price - combine_pay_amt) as total_price')->where($where)->find();
        return $res->total_price + $yueLeft;
        return !$res['total_price'] || $res['total_price']=="0.00" ? 0 : ($res['total_price'] + $yueLeft);
    }

    /**
     * [chainRatio 环比计算]
     * @Author   monster
     * @DateTime 2022-05-25T13:38:35+0800
     * @param    [type]                   $left_operand  [本期数]
     * @param    [type]                   $right_operand [上期数]
     * @param    [type]                   $scale         [description]
     * @return   [type]                                  [description]
     */
    public static function chainRatio($left_operand = 0, $right_operand = 0, $scale = 4)
    {
        if (!$right_operand) {
            $chain_ratio = 0;
        } else {
            $chain_ratio = bcdiv(($left_operand - $right_operand) , $right_operand, $scale) * 100;
        }

        return (string)$chain_ratio;
    }

    /**
     * [setWhere 时间处理]
     * @Author   monster
     * @DateTime 2022-05-23T14:09:53+0800
     * @param    [type]                   $where [description]
     */
    public static function setWhere($where)
    {
        $start_time = 0;
        $end_time = 0;
        $start_time_cr = 0;
        $end_time_cr = 0;
        if (!isset($where['date']) || empty($where['date'])) {
            return false;
        }

        switch ($where['date']) {
            case 'today':
                $start_time = strtotime(date('Y-m-d 00:00:00'));
                $end_time = strtotime(date('Y-m-d 23:59:59'));
                $start_time_cr = strtotime(date('Y-m-d 00:00:00', strtotime("-1 day")));
                $end_time_cr = strtotime(date('Y-m-d 23:59:59', strtotime("-1 day")));
                break;
            case 'yesterday':
                $start_time = strtotime(date('Y-m-d 00:00:00', strtotime("-1 day")));
                $end_time = strtotime(date('Y-m-d 23:59:59', strtotime("-1 day")));
                $start_time_cr = strtotime(date('Y-m-d 00:00:00', strtotime("-2 day")));
                $end_time_cr = strtotime(date('Y-m-d 23:59:59', strtotime("-2 day")));
                break;
            case 'week':
                $start_time = strtotime(date('Y-m-d 00:00:00', strtotime('this week')));
                $end_time = strtotime(date('Y-m-d 23:59:59', strtotime('last day this week +7 day')));
                $start_time_cr = mktime(0,0,0,date('m'),date('d')-date('w')+1-7,date('Y'));
                $end_time_cr = mktime(23,59,59,date('m'),date('d')-date('w')+7-7,date('Y'));
                break;
            case 'month':
                $month = date('Y-m-01');
                $start_time = strtotime(date('Y-m-01 00:00:00'));
                $end_time = strtotime(date('Y-m-d 23:59:59', strtotime("$month +1 month -1 day")));
                $start_time_cr = mktime(00, 00, 00, date('m', strtotime(date('Y-m')))-1, 01);
                $end_time_cr = mktime(23, 59, 59, date('m', strtotime(date('Y-m')))+1-1, 00);
                break;
            case 'year':
                $start_time = strtotime(date('Y-01-01 00:00:00'));
                $end_time = strtotime(date('Y-12-31 23:59:59'));
                $start_time_cr = mktime(0,0,0,1,1,date('Y',time())-1);
                $end_time_cr = mktime(23,59,59,12,31,date('Y',time())-1);
                break;
            default:
                $start_time = strtotime(explode(' - ', $where['date'])[0] . ' 00:00:00');
                $end_time = strtotime(explode(' - ', $where['date'])[1] . ' 23:59:59');
                $cha = $end_time - $start_time;
                $before_s = $start_time - 86400;
                $start_time_cr = strtotime(date('Y-m-d 00:00:00', $start_time - $cha));
                $end_time_cr = strtotime(date('Y-m-d 23:59:59', $before_s));
                break;
        }
        
        return [
            'start_time' => $start_time, 
            'end_time' => $end_time,
            'start_time_cr' => $start_time_cr,
            'end_time_cr' => $end_time_cr
        ];
    }

    /**
     * [getGradeSpecial 获取年级对应的专题id]
     * @Author   monster
     * @DateTime 2022-05-23T15:24:20+0800
     * @param    [type]                   $grade_id [1：小学 2：初中 3：高中]
     * @return   [type]                   [description]
     */
    public static function getGradeSpecial($grade_id)
    {
        $res = SpecialSubject::where('grade_id', $grade_id)->select()->toArray();
        $ids = array_column($res, 'id');
        $data = Special::where('subject_id', 'in', $ids)->where(['is_del'=>0])->select()->toArray();

        return array_column($data, 'id');
    }

    /**
     * [getTradeBadge 交易统计]
     * @Author   monster
     * @DateTime 2022-05-23T14:09:33+0800
     * @param    [type]                   $where [description]
     * @return   [type]                          [description]
     */
    public static function getTradeBadge($where)
    {
        $data = [];
        // 环比初始化 _cr
        $data['order_amount_cr'] = 0;
        $data['order_amount_zfb_cr'] = 0;
        $data['order_amount_wx_cr'] = 0;
        $data['order_amount_yue_cr'] = 0;
        $data['order_count_cr'] = 0;
        $data['order_count_cdkey_cr'] = 0;
        $data['order_count_pink_cr'] = 0;
        $data['order_count_gift_cr'] = 0;
        $data['order_count_seckill_cr'] = 0;
        $data['order_count_primary_cr'] = 0;
        $data['order_amount_primary_cr'] = 0;
        $data['order_count_junior_cr'] = 0;
        $data['order_amount_junior_cr'] = 0;
        $data['order_count_high_cr'] = 0;
        $data['order_amount_high_cr'] = 0;
        $data['order_amount_refund_cr'] = 0;
        $data['order_count_refund_cr'] = 0;
        $data['net_income_cr'] = 0;

        // 查询小初高各自专题集
        $primary = self::getGradeSpecial(1);
        $junior = self::getGradeSpecial(2);
        $high = self::getGradeSpecial(3);

        $time_condition = self::setWhere($where);

        if ($time_condition) {
            $s = $time_condition['start_time'];
            $e = $time_condition['end_time'];
            $s_cr = $time_condition['start_time_cr'];
            $e_cr = $time_condition['end_time_cr'];

            $data['order_amount'] = self::orderAmount($s, $e);
            $data['order_amount_zfb'] = self::orderAmount($s, $e, 'zhifubao');
            $data['order_amount_wx'] = self::orderAmount($s, $e, 'weixin');
            $data['order_amount_yue'] = self::orderAmount($s, $e, 'yue');
            $data['order_count'] = self::orderCount($s, $e);
            $data['order_count_cdkey'] = self::orderCount($s, $e, 1);
            $data['order_count_pink'] = self::orderCount($s, $e, 2);
            $data['order_count_gift'] = self::orderCount($s, $e, 3);
            $data['order_count_seckill'] = self::orderCount($s, $e, 4);
            $data['order_count_primary'] = self::orderCount($s, $e, 0, $primary);
            $data['order_amount_primary'] = self::orderAmount($s, $e, '', $primary);
            $data['order_count_junior'] = self::orderCount($s, $e, 0, $junior);
            $data['order_amount_junior'] = self::orderAmount($s, $e, '', $junior);
            $data['order_count_high'] = self::orderCount($s, $e, 0, $high);
            $data['order_amount_high'] = self::orderAmount($s, $e, '', $high);
            $data['order_amount_refund'] = self::orderAmount($s, $e, '', [], 2);
            $data['order_count_refund'] = self::orderCount($s, $e, '', [], 2);
            $data['net_income'] = $data['order_amount'] - $data['order_amount_yue'];

            $order_amount_old = self::orderAmount($s_cr, $e_cr);
            $order_amount_zfb_old = self::orderAmount($s_cr, $e_cr, 'zhifubao');
            $order_amount_wx_old = self::orderAmount($s_cr, $e_cr, 'weixin');
            $order_amount_yue_old = self::orderAmount($s_cr, $e_cr, 'yue');
            $order_count_old = self::orderCount($s_cr, $e_cr);
            $order_count_cdkey_old = self::orderCount($s_cr, $e_cr, 1);
            $order_count_pink_old = self::orderCount($s_cr, $e_cr, 2);
            $order_count_gift_old = self::orderCount($s_cr, $e_cr, 3);
            $order_count_seckill_old = self::orderCount($s_cr, $e_cr, 4);
            $order_count_primary_old = self::orderCount($s_cr, $e_cr, 0, $primary);
            $order_amount_primary_old = self::orderAmount($s_cr, $e_cr, '', $primary);
            $order_count_junior_old = self::orderCount($s_cr, $e_cr, 0, $junior);
            $order_amount_junior_old = self::orderAmount($s_cr, $e_cr, '', $junior);
            $order_count_high_old = self::orderCount($s_cr, $e_cr, 0, $high);
            $order_amount_high_old = self::orderAmount($s_cr, $e_cr, '', $high);
            $order_amount_refund_old = self::orderAmount($s_cr, $e_cr, '', [], 2);
            $order_count_refund_old = self::orderCount($s_cr, $e_cr, '', [], 2);
            $net_income_old = $order_amount_old - $order_amount_yue_old;
            // 环比数据
            $data['order_amount_cr'] = self::chainRatio($data['order_amount'], $order_amount_old);
            $data['order_amount_zfb_cr'] = self::chainRatio($data['order_amount_zfb'], $order_amount_zfb_old);
            $data['order_amount_wx_cr'] = self::chainRatio($data['order_amount_wx'], $order_amount_wx_old);
            $data['order_amount_yue_cr'] = self::chainRatio($data['order_amount_yue'], $order_amount_yue_old);
            $data['order_count_cr'] = self::chainRatio($data['order_count'], $order_count_old);
            $data['order_count_cdkey_cr'] = self::chainRatio($data['order_count_cdkey'], $order_count_cdkey_old);
            $data['order_count_pink_cr'] = self::chainRatio($data['order_count_pink'], $order_count_pink_old);
            $data['order_count_gift_cr'] = self::chainRatio($data['order_count_gift'], $order_count_gift_old);
            $data['order_count_seckill_cr'] = self::chainRatio($data['order_count_seckill'], $order_count_seckill_old);
            $data['order_count_primary_cr'] = self::chainRatio($data['order_count_primary'], $order_count_primary_old);
            $data['order_amount_primary_cr'] = self::chainRatio($data['order_amount_primary'], $order_amount_primary_old);
            $data['order_count_junior_cr'] = self::chainRatio($data['order_count_junior'], $order_count_junior_old);
            $data['order_amount_junior_cr'] = self::chainRatio($data['order_amount_junior'], $order_amount_junior_old);
            $data['order_count_high_cr'] = self::chainRatio($data['order_count_high'], $order_count_high_old);
            $data['order_amount_high_cr'] = self::chainRatio($data['order_amount_high'], $order_amount_high_old);
            $data['order_amount_refund_cr'] = self::chainRatio($data['order_amount_refund'], $order_amount_refund_old);
            $data['order_count_refund_cr'] = self::chainRatio($data['order_count_refund'], $order_count_refund_old);
            $data['net_income_cr'] = self::chainRatio($data['net_income'], $net_income_old);

        } else {
            $data['order_amount'] = self::orderAmount(0, 0);
            $data['order_amount_zfb'] = self::orderAmount(0, 0, 'zhifubao');
            $data['order_amount_wx'] = self::orderAmount(0, 0, 'weixin');
            $data['order_amount_yue'] = self::orderAmount(0, 0, 'yue');
            $data['order_count'] = self::orderCount(0, 0);
            $data['order_count_cdkey'] = self::orderCount(0, 0, 1);
            $data['order_count_pink'] = self::orderCount(0, 0, 2);
            $data['order_count_gift'] = self::orderCount(0, 0, 3);
            $data['order_count_seckill'] = self::orderCount(0, 0, 4);
            $data['order_count_primary'] = self::orderCount(0, 0, 0, $primary);
            $data['order_amount_primary'] = self::orderAmount(0, 0, '', $primary);
            $data['order_count_junior'] = self::orderCount(0, 0, 0, $junior);
            $data['order_amount_junior'] = self::orderAmount(0, 0, '', $junior);
            $data['order_count_high'] = self::orderCount(0, 0, 0, $high);
            $data['order_amount_high'] = self::orderAmount(0, 0, '', $high);
            $data['order_amount_refund'] = self::orderAmount(0, 0, '', [], 2);
            $data['order_count_refund'] = self::orderCount(0, 0, '', [], 2);
            $data['net_income'] = $data['order_amount'] - $data['order_amount_yue'];
        }

        return $data;
    }

    /**
     * [getUserBadge 用户统计]
     * @Author   monster
     * @DateTime 2022-05-23T17:05:23+0800
     * @param    [type]                   $where [description]
     * @return   [type]                          [description]
     */
    public static function getUserBadge($where)
    {
        $data = [];
        $time_condition = self::setWhere($where);

        $s = $time_condition['start_time'];
        $e = $time_condition['end_time'];
        $s_cr = $time_condition['start_time_cr'];
        $e_cr = $time_condition['end_time_cr'];

        $data['view_count'] = self::userVisitCount($s, $e);
        $data['new_user_count'] = self::newUserCount($s, $e);
        $data['active_user_count'] = self::userActiveCount($s, $e);
        $data['user_order_count'] = self::userOrderCount($s, $e);
        
        $view_count_old = self::userVisitCount($s_cr, $e_cr);
        $new_user_count_old = self::newUserCount($s_cr, $e_cr);
        $active_user_count_old = self::userActiveCount($s_cr, $e_cr);
        $user_order_count_old = self::userOrderCount($s_cr, $e_cr);

        $data['view_count_cr'] = self::chainRatio($data['view_count'], $view_count_old);
        $data['new_user_count_cr'] = self::chainRatio($data['new_user_count'], $new_user_count_old);
        $data['active_user_count_cr'] = self::chainRatio($data['active_user_count'], $active_user_count_old);
        $data['user_order_count_cr'] = self::chainRatio($data['user_order_count'], $user_order_count_old);

        return $data;
    }

    /**
     * [getUserMapTable 用户地图分布表]
     * @Author   monster
     * @DateTime 2022-05-25T17:24:14+0800
     * @return   [type]                   [description]
     */
    public static function getUserMapTable($where)
    {
        $data = [];
        $time_condition = self::setWhere($where);
        $s = $time_condition['start_time'];
        $e = $time_condition['end_time'];
        $user_pro = self::userPro();

        foreach ($user_pro as &$item) {
            $item['new_user'] = self::newUserCount($s, $e, $item['reg_pro']);
            $item['visit_user'] = self::userVisitCountByPro($s, $e, $item['reg_pro']);
            $item['user_pay'] = self::userPayByPro($s, $e, $item['reg_pro']);
        }

        return $user_pro;
    }

    /**
     * [getSpecialBadge 专题统计]
     * @Author   monster
     * @DateTime 2022-05-24T09:27:11+0800
     * @param    [type]                   $where [description]
     * @return   [type]                          [description]
     */
    public static function getSpecialBadge($where)
    {
        $data = [];
        // 环比初始化 _cr
        $data['special_view_count_cr'] = 0;
        $data['special_user_count_cr'] = 0;
        $data['order_count_cr'] = 0;
        $data['order_amount_cr'] = 0;
        $data['order_count_cdkey_cr'] = 0;
        $data['order_amount_refund_cr'] = 0;
        $data['net_income_cr'] = 0;
        $data['pay_conversion_rate_cr'] = 0;

        $time_condition = self::setWhere($where);

        if ($time_condition) {
            $s = $time_condition['start_time'];
            $e = $time_condition['end_time'];
            $s_cr = $time_condition['start_time_cr'];
            $e_cr = $time_condition['end_time_cr'];

            $data['special_view_count'] = self::userVisitCount($s, $e, 'wap', 'special', 'details');
            $data['special_user_count'] = self::userVisitCount($s, $e, 'wap', 'special', 'details', 1);
            $data['order_count'] = self::orderCount($s, $e);
            $data['order_amount'] = self::orderAmount($s, $e);
            $data['order_count_cdkey'] = self::orderCount($s, $e, 1);
            $data['order_amount_refund'] = self::orderAmount($s, $e, '', [], 2);
            $data['net_income'] = (string)($data['order_amount'] - $data['order_amount_refund']);
            $pay_user_count = self::userOrderCount($s, $e);
            $data['pay_conversion_rate'] = $data['special_user_count'] ? (string)(bcdiv($pay_user_count, $data['special_user_count'], 4) * 100) : 0;

            $special_view_count_old = self::userVisitCount($s_cr, $e_cr, 'wap', 'special', 'details');
            $special_user_count_old = self::userVisitCount($s_cr, $e_cr, 'wap', 'special', 'details', 1);
            $order_count_old = self::orderCount($s_cr, $e_cr);
            $order_amount_old = self::orderAmount($s_cr, $e_cr);
            $order_count_cdkey_old = self::orderCount($s_cr, $e_cr, 1);
            $order_amount_refund_old = self::orderAmount($s_cr, $e_cr, '', [], 2);
            $net_income_old = (string)($order_amount_old - $order_amount_refund_old);
            $pay_user_count_old = self::userOrderCount($s_cr, $e_cr);
            $pay_conversion_rate_old = $special_user_count_old ? (string)(bcdiv($pay_user_count_old, $special_user_count_old, 4) * 100) : 0;

            $data['special_view_count_cr'] = self::chainRatio($data['special_view_count'], $special_view_count_old);
            $data['special_user_count_cr'] = self::chainRatio($data['special_user_count'], $special_user_count_old);
            $data['order_count_cr'] = self::chainRatio($data['order_count'], $order_count_old);
            $data['order_amount_cr'] = self::chainRatio($data['order_amount'], $order_amount_old);
            $data['order_count_cdkey_cr'] = self::chainRatio($data['order_count_cdkey'], $order_count_cdkey_old);
            $data['order_amount_refund_cr'] = self::chainRatio($data['order_amount_refund'], $order_amount_refund_old);
            $data['net_income_cr'] = self::chainRatio($data['net_income'], $net_income_old);
            $data['pay_conversion_rate_cr'] = self::chainRatio($data['pay_conversion_rate'], $pay_conversion_rate_old);
        } else {
            $data['special_view_count'] = self::userVisitCount(0, 0, 'wap', 'special', 'details');
            $data['special_user_count'] = self::userVisitCount(0, 0, 'wap', 'special', 'details', 1);
            $data['order_count'] = self::orderCount(0, 0);
            $data['order_amount'] = self::orderAmount(0, 0);
            $data['order_count_cdkey'] = self::orderCount(0, 0, 1);
            $data['order_amount_refund'] = self::orderAmount(0, 0, '', [], 2);
            $data['net_income'] = (string)($data['order_amount'] - $data['order_amount_refund']);
            $pay_user_count = self::userOrderCount(0, 0);
            $data['pay_conversion_rate'] = $data['special_user_count'] ? (string)(bcdiv($pay_user_count, $data['special_user_count']+130000, 4) * 100) : 0;
        }

        return $data;
    }

    /**
     * [specialStatistics 专题统计表格]
     * @Author   monster
     * @DateTime 2022-05-24T14:21:11+0800
     * @param    [type]                   $where [description]
     * @return   [type]                          [description]
     */
    public static function specialStatistics($where)
    {
        $data = [];
        $time_condition = self::setWhere($where);
        if ($time_condition) {
            $s = $time_condition['start_time'];
            $e = $time_condition['end_time'];

            $res = self::specialCount($where, $s, $e);
        } else {
            $res = self::specialCount($where);
        }

        return ['count'=>$res['count'], 'data'=>$res['data']];
    }

    /**
     * [specialCount description]
     * @Author   monster
     * @DateTime 2022-05-24T14:26:27+0800
     * @return   [type]                   [description]
     */
    public static function specialCount($where, $start_time = 0, $end_time = 0)
    {
        $vFilter = "vlog.module='wap' AND vlog.controller='special' AND vlog.action = 'details'";
        $model = VisitLog::alias('vlog')->where(['vlog.module' => 'wap' , 'vlog.controller' => 'special', 'vlog.action' => 'details'])
                 ->join('__SPECIAL__ s' , 'vlog.param_id = s.id')
                 ->group('param_id');

        $timeFilter = '1=1';
        if ($start_time && $end_time) {
            $model = $model->where('vlog.add_time', 'between', [$start_time, $end_time]);

            $timeFilter .= sprintf(" AND vlog.add_time BETWEEN %d AND %d" , $start_time, $end_time);
        }

        $data  = [];
        $count = $model->count();
        if ( $count == 0 ) {
            return compact('count','data');
        }

        $sql = sprintf("
            SELECT
            vlog.special_id,ifnull(vlog.view_count,0) as view_count
            ,ifnull(sorder.order_num,0) as order_num, ifnull(sorder.sales,0) as sales
            ,ifnull(order_cdk.cdkey_num,0) as cdkey_num
            ,s.title,s.image
            FROM `eb_special` s
            INNER JOIN (
                SELECT param_id as special_id,count('vlog.id') as view_count
                FROM `eb_visit_log` vlog
                WHERE %s AND %s AND param_id > 0
                GROUP BY controller,action,param_id
            ) as vlog ON s.id = vlog.special_id
            LEFT JOIN (
                SELECT vlog.cart_id as special_id,count('vlog.id') as order_num,SUM(vlog.pay_price) as sales
                FROM `eb_store_order` vlog
                WHERE vlog.is_del = 0 AND vlog.paid = 1 AND vlog.refund_status = 0 AND %s
                GROUP BY cart_id
            ) as sorder ON vlog.special_id = sorder.special_id
            LEFT JOIN (
                SELECT vlog.cart_id as special_id,count('vlog.id') as cdkey_num
                FROM `eb_store_order` vlog
                WHERE vlog.is_del = 0 AND vlog.paid = 1 AND vlog.refund_status = 0 AND vlog.cdkey != '' AND %s
                GROUP BY cart_id
            ) as order_cdk ON vlog.special_id = order_cdk.special_id
            ORDER BY sorder.sales DESC
        " , $vFilter , $timeFilter, $timeFilter , $timeFilter);

        $offset = isset($where['page']) ? (int)$where['page'] - 1 : 0;
        if (!isset($where['excel']) || $where['excel'] == 0) {
            $sql .= sprintf(" LIMIT %d,%d" , ($where['page'] - 1) * $where['limit']  , $where['limit']);
        }

        $data = self::query($sql);
        foreach ($data as $key => $value) {
            if ($key < 5) {
                $data[$key]['special_sort'] = 'TOP' . ($key + 1);
            } else {
                $data[$key]['special_sort'] = $key + 1;
            }

            $data[$key]['order_from'] = [
                'common' => self::_orderCount($start_time, $end_time, 0, $value['special_id'], 0, 1),
                'index_banner' => self::_orderCount($start_time, $end_time, 0, $value['special_id'], 0, 2),
                'index_zone' => self::_orderCount($start_time, $end_time, 0, $value['special_id'], 0, 3),
                'index_activity' => self::_orderCount($start_time, $end_time, 0, $value['special_id'], 0, 4),
                'recommend' => self::_orderCount($start_time, $end_time, 0, $value['special_id'], 0, 5),
            ];
        }

        if (isset($where['excel']) && $where['excel'] == 1) {
            return self::saveExcel($data);;
        }

        return compact('count','data');

        $model = StoreOrder::where(['o.is_del' => 0, 'o.refund_status' => 0, 'o.total_num' => 1, 'o.paid' => 1])->alias('o')->field('count(*) as order_num,sum(o.pay_price) as sales,s.title,s.image,s.id');
        if ($start_time && $end_time) {
            $model = $model->where('o.add_time', 'between', [$start_time, $end_time]);
        }
        $model = $model->join('__SPECIAL__ s', 's.id=o.cart_id','left')->group('o.cart_id')->order('order_num desc');

        if (isset($where['excel']) && $where['excel'] == 1) {
            $data = ($data = $model->select()) && count($data) ? $data->toArray() : [];
        } else {
            $data = ($data = $model->page((int)$where['page'], (int)$where['limit'])->select()) && count($data) ? $data->toArray() : [];
        }

        // 格式化专题列表
        foreach ($data as $key => $value) {
            if ($key < 5) {
                $data[$key]['special_sort'] = 'TOP' . ($key + 1);
            } else {
                $data[$key]['special_sort'] = $key + 1;
            }
            $data[$key]['view_count'] = self::userVisitCount($start_time, $end_time, 'wap', 'special', 'details', 0, $value['id']);
            $data[$key]['cdkey_num'] = self::orderCount($start_time, $end_time, 1, $value['id']);
            $data[$key]['order_from'] = [
                'common' => self::orderCount($start_time, $end_time, 0, $value['id'], 0, 1),
                'index_banner' => self::orderCount($start_time, $end_time, 0, $value['id'], 0, 2),
                'index_zone' => self::orderCount($start_time, $end_time, 0, $value['id'], 0, 3),
                'index_activity' => self::orderCount($start_time, $end_time, 0, $value['id'], 0, 4),
                'recommend' => self::orderCount($start_time, $end_time, 0, $value['id'], 0, 5),
            ];
        }
        
        if (isset($where['excel']) && $where['excel'] == 1) {
            // 导出excel
            self::saveExcel($data);
        }

        // 数量
        $model1 = StoreOrder::where(['o.is_del' => 0, 'o.refund_status' => 0, 'o.total_num' => 1, 'o.paid' => 1])->alias('o')->field('count(*) as order_num,sum(o.pay_price) as sales,s.title,s.image,s.id');
        if ($start_time && $end_time) {
            $model1 = $model1->where('o.add_time', 'between', [$start_time, $end_time]);
        }
        $count = $model1->join('__SPECIAL__ s', 's.id=o.cart_id','left')->group('o.cart_id')->count();

        return compact('count','data');
    }

    private static function _orderCount ( $start_time, $end_time, $qu1, $special_id, $qu2, $type )
    {
        if ( $end_time == 0 ) $end_time = time();

        return StoreOrder::where([
            'cart_id'   => $special_id,
            'paid'      => 1,
            'order_from' => $type,
        ])->where('add_time' , 'between' , [$start_time , $end_time])
        ->count();
    }

    /**
     * [saveExcel 导出excel]
     * @Author   monster
     * @DateTime 2022-05-24T16:51:34+0800
     * @param    [type]                   $data [description]
     * @return   [type]                         [description]
     */
    public static function saveExcel($data)
    {
        $export = [];
        foreach ($data as $item) {
            $orderFrom = '首页banner：' . $item['order_from']['index_banner'] . PHP_EOL .
                         '首页专区：' . $item['order_from']['index_zone'] . PHP_EOL .
                         '首页活动：' . $item['order_from']['index_activity'] . PHP_EOL .
                         '今日推荐：' . $item['order_from']['recommend'];
            $export[] = [
                $item['special_sort'],
                $item['title'],
                $item['view_count'],
                $item['order_num'],
                $item['cdkey_num'],
                $item['sales'],
                $orderFrom
            ];
        }
        PHPExcelService::setExcelHeader(['排名','名称','浏览量','订单数','兑换数量','销售额','订单来源'])
            ->setExcelTile('专题统计导出','专题信息'.time(),' 生成时间：'.date('Y-m-d H:i:s',time()))
            ->setExcelContent($export)
            ->ExcelSave();
    }
}